Method of generating database transaction statements based on existing queries

ABSTRACT

The present invention is generally directed providing a method, system and article of manufacture for automatically generating database transaction statements (such as queries, inserts, updates, and deletes) based on fields from existing queries and user input. By identifying fields of existing queries (or statements) and gathering attributes of those fields, a transaction generator can be configured to provide users with a selection of generated transactions that can be performed on data that corresponds to the selected fields. The selection of transactions may be provided to users via a variety of interfaces including graphic interfaces and plug-in components utilized by external applications

BACKGROUND OF THE INVENTION

1. Field of the Invention

The present invention generally relates to data processing, and moreparticularly, to the generation of database transactions based onattributes of existing queries.

2. Description of the Related Art

Databases are computerized information storage and retrieval systems. Arelational database management system is a computer database managementsystem (DBMS) that uses relational techniques for storing and retrievingdata. The most prevalent type of database is the relational database, atabular database in which data is defined so that it can be reorganizedand accessed in a number of different ways. A distributed database isone that can be dispersed or replicated among different points in anetwork. An object-oriented programming database is one that iscongruent with the data defined in object classes and subclasses.

Regardless of the particular architecture, in a DBMS, a requestingentity (e.g., an application or the operating system) demands access toa specified database by issuing a database access request. Such requestsmay include, for instance, simple catalog lookup requests ortransactions and combinations of transactions that operate to read,change and add specified records in the database. These requests aremade using high-level query languages such as the Structured QueryLanguage (SQL). Illustratively, SQL is used to make interactive queriesfor getting information from and updating a database such asInternational Business Machines' (IBM) DB2, Microsoft's SQL Server, anddatabase products from Oracle, Sybase, and Computer Associates. The term“query” denominates a set of commands for retrieving data from a storeddatabase. Queries take the form of a command language that letsprogrammers and programs select, insert, update, find out the locationof data, and so forth.

One of the issues faced by data mining and database query applications,in general, is their close relationship with a given database schema(e.g., a relational database schema). This relationship makes itdifficult to support an application as changes are made to thecorresponding underlying database schema. Further, the migration of theapplication to alternative underlying data representations is inhibited.In today's environment, the foregoing disadvantages are largely due tothe reliance applications have on SQL, which presumes that a relationalmodel is used to represent information being queried. Furthermore, agiven SQL query is dependent upon a particular relational schema sincespecific database tables, columns and relationships are referencedwithin the SQL query representation. As a result of these limitations, anumber of difficulties arise.

One difficulty is that changes in the underlying relational data modelrequire changes to the SQL foundation that the corresponding applicationis built upon. Therefore, an application designer must either forgochanging the underlying data model to avoid application maintenance ormust change the application to reflect changes in the underlyingrelational model. Another difficulty is that extending an application towork with multiple relational data models requires separate versions ofthe application to reflect the unique SQL requirements driven by eachunique relational schema. Yet another difficulty is evolution of theapplication to work with alternate data representations because SQL isdesigned for use with relational systems. Extending the application tosupport alternative data representations, such as XML, requiresrewriting the application's data management layer to use non-SQL dataaccess methods.

A typical approach used to address the foregoing problems is softwareencapsulation. Software encapsulation involves using a softwareinterface or component to encapsulate access methods to a particularunderlying data representation. An example is found in the EnterpriseJavaBean (EJB) specification that is a component of the Java 2Enterprise Edition (J2EE) suite of technologies. In accordance with theEJB specification, entity beans serve to encapsulate a given set ofdata, exposing a set of Application Program Interfaces (APIs) that canbe used to access this information. This is a highly specializedapproach requiring the software to be written (in the form of new entityEJBs) whenever a new set of data is to be accessed or when a new patternof data access is desired. The EJB model also requires a code update,application built and deployment cycle to react to reorganization of theunderlying physical data model or to support alternative datarepresentations. EJB programming also requires specialized skills sincemore advanced Java programming techniques are involved. Accordingly, theEJB approach and other similar approaches are rather inflexible andcostly to maintain for general-purpose query applications accessing anevolving physical data model.

Another shortcoming of the prior art is that creating and managingdatabase transaction statements (including queries, inserts, updates,and deletes) can be a very inefficient and complex process. A largevariety of transactions can be submitted to the database. However, notall these transactions may be appropriate. Database administrators mustwork closely with data stewards and end users to ensure that eachtransaction acts on the data within the database in an appropriatemanner. Generally, a significant amount of communication between thedatabase administrator and the end users is required—this reducesdatabase availability while raising the database operating cost.

Therefore, what is needed is a system that significantly simplifies datamanagement tasks of administrators and users. The system should assistadministrators in enabling users and managing user transactions. Users,on the other hand, should be able to modify data within the databasewithout needing technical knowledge of databases and of database schemaswithin a specific instance.

SUMMARY OF THE INVENTION

The present invention provides a method, system and article ofmanufacture for generating database transaction statements based onexisting database statements.

For some embodiments, a method for generating a transactional databasestatement based on an existing database statement generally includesparsing the existing database statement to identify fields andcorresponding field attributes, utilizing one or more interfaces forreceiving input regarding the transactional statement, and generatingthe transactional database statement based on the identified fields anduser input.

Another embodiment provides a computer-readable medium containing aprogram for generating a transactional database statement based on anexisting database statement. When executed by a processor, the programperforms operations generally including parsing the existing databasestatement to identify fields and corresponding field attributes,generating one or more interfaces for receiving input regarding thetransactional statement, and generating the transactional databasestatement based on the identified fields and user input.

Yet another embodiment provides a data processing system that generallyincludes a database, an existing query statement, a query interfaceallowing users to issue query statements against the database, and atransaction manager configured to generate a transactional statementagainst the database based on fields and corresponding field attributesof the existing query statement.

BRIEF DESCRIPTION OF THE DRAWINGS

So that the manner in which the above recited features, advantages andobjects of the present invention are attained and can be understood indetail, a more particular description of the invention, brieflysummarized above, may be had by reference to the embodiments thereofwhich are illustrated in the appended drawings.

It is to be noted, however, that the appended drawings illustrate onlytypical embodiments of this invention and are therefore not to beconsidered limiting of its scope, for the invention may admit to otherequally effective embodiments.

FIG. 1 is a computer system illustratively utilized in accordance withthe present invention.

FIG. 2A is a relational view of software components of one embodiment ofthe invention.

FIG. 2B is a chart illustrating fields of Data Repository Abstractioncomponents and their attributes.

FIG. 3A is a flow chart illustrating exemplary operations for generatingdatabase transactions according to aspects of the present invention.

FIG. 3B is a flow chart illustrating exemplary operations for generatinginsert, update and delete based transaction statements.

FIGS. 4A-4G illustrate exemplary graphical user interface (GUI) screensin accordance with one embodiment of the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

The present invention provides a method, system and article ofmanufacture for automatically generating database transaction statements(such as queries, inserts, updates, and deletes) based on user input andfields from existing queries. By identifying fields of existing queries(or statements) and gathering attributes of those fields, a transactiongenerator can be configured to provide users with a selection ofgenerated transactions that can be performed on data that corresponds tothe selected fields. The selection of transactions may be provided tousers via a variety of interfaces including graphic interfaces andplug-in components utilized by external applications.

As used herein, the term database generally refers to a collection ofdata arranged for ease and speed of search and retrieval. Further, adatabase comprises logical and physical structures. While the followingdescription focuses on generating transaction statements for relationaldatabases, those skilled in the art will recognize the methods describedherein may be used with any type of database including anobject-relational database and an XML-based database.

As used herein, the term field may correspond to a column within a tableof a relational database. Those skilled in the art will recognize thatthe term field may also represent a component of logical records locatedwithin data structures of a variety of databases including objectrelational databases and XML-based databases.

As used herein, the term query generally refers to statements designedto retrieve data from the database. For example, the query may be a“select” statement written using SQL. However the term query may also beused to generally describe any database transaction statement, includingan insert, update or delete statement.

As used herein, the term metadata refers to descriptive informationincluding but not limited to: the attributes of a processingenvironment, an application, users of the application and other manageddata in the system. Metadata based on managed data includes descriptiveinformation about the context and characteristics of data managed withinthe system. Security oriented user information, such as usercredentials, and user invoked session information also constitutesmetadata.

As used herein, the term user may generally apply to any entityutilizing the database processing system described herein, such as aperson (e.g., an individual) interacting with an application program oran application program itself, for example, performing automated tasks.While the following description may often refer to a graphical userinterface (GUI) intended to present information to and receiveinformation from a person, it should be understood that in many cases,the same functionality may be provided through a non-graphical userinterface, such as a command line and, further, similar information maybe exchanged with a non-person user via a programming interface.

In one embodiment, a particular data definition framework (referred toherein as a data repository abstraction (DRA) layer/component) isprovided for accessing and modifying (inserting, updating or deleting)data independent of the particular manner in which the data isphysically represented. The data may be located in a single repository(i.e., source) of data or a plurality of different data repositories.Thus, the data definition framework may provide a logical view of one ormore underlying data repositories.

One embodiment of the invention is implemented as a program product foruse with a computer system and described below. The program(s) of theprogram product defines functions of the embodiments (including themethods described herein) and can be contained on a variety ofsignal-bearing media. Illustrative signal-bearing media include, but arenot limited to: (i) information permanently stored on non-writablestorage media (e.g., read-only memory devices within a computer such asCD-ROM disks readable by a CD-ROM drive); (ii) alterable informationstored on writable storage media (e.g., floppy disks within a diskettedrive or hard-disk drive); or (iii) information conveyed to a computerby a communications medium, such as through a computer or telephonenetwork, including wireless communications. The latter embodimentspecifically includes information downloaded from the Internet and othernetworks. Such signal-bearing media, when carrying computer-readableinstructions that direct the functions of the present invention,represent embodiments of the present invention.

In general, the routines executed to implement the embodiments of theinvention, may be part of an operating system or a specific application,component, program, module, object, or sequence of instructions. Thesoftware of the present invention typically is comprised of a multitudeof instructions that will be translated by the native computer into amachine-readable format and hence executable instructions. Also,programs are comprised of variables and data structures that eitherreside locally to the program or are found in memory or on storagedevices. In addition, various programs described hereinafter may beidentified based upon the application for which they are implemented ina specific embodiment of the invention. However, it should beappreciated that any particular nomenclature that follows is used merelyfor convenience, and thus the invention should not be limited to usesolely in any specific application identified and/or implied by suchnomenclature.

Exemplary Application Environment

FIG. 1 depicts a block diagram of a networked system 100 in whichembodiments of the present invention may be implemented. In general, thenetworked system 100 includes a client (i.e., generally any requestingentity such as a user or application) computer 102 (three such clientcomputers 102 are shown) and at least one server computer 104. Theclient computer 102 and the server computer 104 are connected via anetwork 126. In general, the network 126 may be a local area network(LAN) and/or a wide area network (WAN). In a particular embodiment, thenetwork 126 is the Internet. However, it is noted that aspects of theinvention need not be implemented in a distributed environment. As such,the client computers 102 and the server computer 104 are more generallyrepresentative of any requesting entity (such as a user or application)issuing queries and a receiving entity configured to handle the queries,respectively.

The client computer 102 includes a Central Processing Unit (CPU) 110connected via a bus 130 to a memory 112, storage 114, an input device116, an output device 119, and a network interface device 118. The inputdevice 116 can be any device to give input to the client computer 102.For example, a keyboard, keypad, light-pen, touch-screen, track-ball, orspeech recognition unit, audio/video player, and the like could be used.The output device 119 can be any device to give output to the user,e.g., any conventional display screen. Although shown separately fromthe input device 116, the output device 119 and input device 116 couldbe combined. For example, a display screen with an integratedtouch-screen, a display with an integrated keyboard, or a speechrecognition unit combined with a text speech converter could be used.

The network interface device 118 may be any entry/exit device configuredto allow network communications between the client computer 102 and theserver computer 104 via the network 126. For example, the networkinterface device 118 may be a network adapter or other network interfacecard (NIC).

Storage 114 is preferably a Direct Access Storage Device (DASD).Although it is shown as a single unit, it could be a combination offixed and/or removable storage devices, such as fixed disc drives,floppy disc drives, tape drives, removable memory cards, or opticalstorage. The memory 112 and storage 114 could be part of one virtualaddress space spanning multiple primary and secondary storage devices.

The memory 112 is preferably a random access memory sufficiently largeto hold the necessary programming and data structures of the invention.While the memory 112 is shown as a single entity, it should beunderstood that the memory 112 may in fact comprise a plurality ofmodules, and that the memory 112 may exist at multiple levels, from highspeed registers and caches to lower speed but larger DRAM chips.

Illustratively, the memory 112 contains an operating system 124.Illustrative operating systems, which may be used to advantage, includeLinux and Microsoft's Windows®. More generally, any operating systemsupporting the functions disclosed herein may be used.

The memory 112 is also shown containing a browser program 122 that, whenexecuted on CPU 110, provides support for navigating between the variousservers 104 and locating network addresses at one or more of the servers104. In one embodiment, the browser program 122 includes a web-basedGraphical User Interface (GUI), which allows the user to display HyperText Markup Language (HTML) information. More generally, however, thebrowser program 122 may be any GUI-based program capable of renderingthe information transmitted from the server computer 104.

The server computer 104 may be physically arranged in a manner similarto the client computer 102. Accordingly, the server computer 104 isshown generally comprising a CPU 130, a memory 132, and a storage device134, coupled to one another by a bus 136. Memory 132 may be a randomaccess memory sufficiently large to hold the necessary programming anddata structures that are located on the server computer 104.

The server computer 104 is generally under the control of an operatingsystem 138 shown residing in memory 132. Examples of the operatingsystem 138 include IBM OS/400®, UNIX, Microsoft Windows®, and the like.More generally, any operating system capable of supporting the functionsdescribed herein may be used.

The memory 132 further includes one or more applications 140 and anabstract query interface 146. The applications 140 and the abstractquery interface 146 are software products comprising a plurality ofinstructions that are resident at various times in various memory andstorage devices in the computer system 100. When read and executed byone or more processors 130 in the server 104, the applications 140 andthe abstract query interface 146 cause the computer system 100 toperform the steps necessary to execute steps or elements embodying thevarious aspects of the invention. The applications 140 (and moregenerally, any requesting entity, including the operating system 138and, at the highest level, users) issue queries against a database.

Illustrative sources against which queries may be issued include localdatabases 156 ₁ . . . 156 _(N), and remote databases 157 ₁ . . . 157_(N), collectively referred to as database(s) 156-157). Illustratively,the databases 156 are shown as part of a database management system(DBMS) 154 in storage 134. More generally, as used herein, the term“databases” refers to any collection of data regardless of theparticular physical representation. By way of illustration, thedatabases 156-157 may be organized according to a relational schema(accessible by SQL queries) or according to an XML schema (accessible byXML queries). However, the invention is not limited to a particularschema and contemplates extension to schemas presently unknown. As usedherein, the term “schema” generically refers to a particular arrangementof data which is described by a data definition framework, such as anabstract data model described herein.

In some cases, queries issued by the applications 140 may be predefined(i.e., hard coded as part of the applications 140) or may be generatedin response to input (e.g., user input) received via a query interface142. The query interface 142 may provide one or more interfaces to auser (e.g., a human user or other application) allowing thespecification of parameters to be used in generating a query. For someembodiments, the interfaces may include a graphical user interface(GUI). The content of the GUIs is generated by the application(s) 140.In a particular embodiment, the GUI content is hypertext markup language(HTML) content which may be rendered on the client computer systems 102with the browser program 122. Accordingly, the memory 132 includes aHypertext Transfer Protocol (http) server process 138 (e.g., a webserver) adapted to service requests from the client computer 102. Forexample, the process 138 may respond to requests to access a database(s)156, which illustratively resides on the server 104. Incoming clientrequests for data from a database 156-157 invoke an application 140.

When executed by the processor 130, the application 140 causes theserver computer 104 to perform the steps or elements embodying thevarious aspects of the invention, including accessing the database(s)156-157. In one embodiment, the application 140 comprises a plurality ofservlets configured to build GUI elements, which are then rendered bythe browser program 122. Where the remote databases 157 are accessed viathe application 140, the data repository abstraction component 148 isconfigured with a location specification identifying the databasecontaining the data to be retrieved. This latter embodiment will bedescribed in more detail below.

FIG. 1 is merely one hardware/software configuration for the networkedclient computer 102 and server computer 104. Embodiments of the presentinvention can apply to any comparable hardware configuration, regardlessof whether the computer systems are complicated, multi-user computingapparatus, single-user workstations, or network appliances that do nothave non-volatile storage of their own. Further, it is understood thatwhile reference is made to particular markup languages, including HTML,the invention is not limited to a particular language, standard orversion. Accordingly, persons skilled in the art will recognize that theinvention is adaptable to other markup languages as well as non-markuplanguages and that the invention is also adaptable future changes in aparticular markup language as well as to other languages presentlyunknown. Likewise, the http server process 138 shown in FIG. 1 is merelyillustrative and other embodiments adapted to support any known andunknown protocols are contemplated.

In any case, as illustrated, the application 140 may have an associatedtransaction manager 144 generally configured to automatically generatedatabase transaction statements (such as queries, inserts, updates, anddeletes) based on user input and fields from existing queries. Forexample, by identifying fields of existing queries (or statements) andgathering attributes of those fields, the transaction generator 144 maybe configured to provide users with a selection of generatedtransactions that can be performed on data that corresponds to theselected fields. As will be described in greater detail below, theselection of transactions (automatically generated or to be generated)by the transaction manager 144 may be provided to users via a variety ofinterfaces including GUIs and plug-in components utilized by externalapplications.

An Exemplary Runtime Environment

FIG. 2A illustrates a relational view of the query interface 142 and thetransaction generator 144 used to generate transactional statements 147based on existing queries. As illustrated, the existing queries mayinclude queries being generated by the query interface 142 or previouslygenerated and saved queries 145. Further, the existing queries may bequeries designed to return data (e.g., with SELECT statements) or, insome cases, transactional statements themselves.

As illustrated, for some embodiments, the queries (referred to herein as“abstract queries”) may be composed using logical fields defined by theabstract query interface 146. In particular, the logical fields used inthe abstract queries are defined by a data repository abstractioncomponent 148. The abstract queries are executed by a runtime component150 which transforms the abstract queries into a form (referred toherein as a concrete query) consistent with the physical representation(e.g., XML, SQL, or other type representation) of the data contained inone or more of the databases 156-157. The queries may be configured toaccess the data and return results, or to modify (i.e., insert, deleteor update) the data.

Abstract queries may be executed by the query execution component 150.In the exemplary abstract data model, the logical fields are definedindependently of the underlying data representation being used in theDBMS 154, thereby allowing queries to be formed that are loosely coupledto the underlying data representation 214. The query execution component150 is generally configured to transform abstract queries into concretequeries compatible with the physical data representation (e.g., an XMLrepresentation 214 ₁, an SQL representation 214 ₂, or any other type ofrepresentation 214 ₃), by mapping the logical fields of the abstractqueries to the corresponding physical fields of the physical datarepresentation 214. The mapping of abstract queries to concrete queries,by the query execution component 150, is described in detail in thecommonly owned, co-pending U.S. patent application Ser. No. 10/083,075,entitled “Application Portability And Extensibility Through DatabaseSchema And Query Abstraction,” filed Feb. 26, 2002.

Generating Transactional Query Statements

FIGS. 3A and 3B are flow diagrams of exemplary operations 300 for thegeneration of database transaction statements based on user input andattributes of fields from existing queries. The operations 300 may bedescribed with reference to FIGS. 2A and 2B and may be performed, forexample, to offer users an opportunity to select and submitautomatically generated transactions based on fields listed in existingqueries.

FIG. 3A is a high-level flow diagram of exemplary operations 300 for thegeneration of a database transaction statement that begin at step 302,when an existing query is selected. As used herein, the term querygenerally refers to any database transaction statements including butnot limited to selects, updates, inserts and deletes. An existing query145 may be any executed or unexecuted statement residing in arepository, cache, or buffer that is accessible by the transactiongenerator 144.

After an existing query is selected, the query's fields and fieldattributes are gathered and analyzed at step 304. Fields may haveassociated attributes such as those shown in FIG. 2B. For example, if afield requires a non-null value, this attribute is among thoseconsidered by the transaction generator. Once all the fields areconsidered by the transaction generator, a metadata repository may bereferenced to determine the types of transactions that may be performedwith respect to the selected fields. For instance, insert, update, anddelete statements may be suitable for some fields, while only updatesand deletes may be appropriate for other fields.

At step 306, users are presented with one or more interfaces from whichthey can select transactions to be submitted to the database. Theselection of transactions may be provided to users via a variety ofinterfaces including graphical interfaces and plug-in componentsutilized by external applications. Based on user input and fieldattributes, at step 308, transaction statements are assembled andprepared to be submitted to the database. FIGS. 4A-4G are GUI screensthat illustrate one implementation of the steps listed above.

For example, FIG. 4A illustrates a screen from a query buildingapplication that includes plug-in components. Users are allowed to inputquery statements in a free-form text box 434 and then submit thetransaction to the query execution runtime. In this embodiment of theinvention, transaction based plug-in components (insert record 431,update record 432, and delete record 433) are presented as links on astandard GUI form. If any of these links are selected, the transactiongenerator will execute the appropriate process based on transactiontype. FIGS. 4B-4G illustrate exemplary GUI forms that may be utilizedwhen generating and issuing transactional statements.

FIG. 3B provides a more detailed flow diagram of exemplary operations300 described above and focuses on the process flow corresponding todifferent types of transactions—illustratively, inserts, updates anddeletes. At step 335, the transaction generator gathers and analyzesfields utilized in an existing query. Along with field attributes, thetransaction generator also considers database privileges and securitycredentials of the user or calling program. For example, some users mayonly have insert privileges for a specific database; the transactiongenerator will not allow these users to select update or deletestatements for submission to the database. The transaction generator mayproduce several types of transaction statements including inserts 340,updates 350, and deletes 360.

Insert Statements

For inserts, at step 345, a list containing all required and optionalfields is compiled; this may be accomplished by referring to availablemetadata. Metadata may provide information such as which table the fieldbelongs to and what the required fields for that table are. Next, aform, such as one shown in FIG. 4B, may be generated to allow users toinput values for each of the fields. Some of the fields may bepre-populated with default values. Further, the form may includefunctionality to work in conjunction with the database management systemso that users are prevented from entering invalid values for specificfields. For example, if insertion of a particular value would result ina primary key violation within the table containing the field, the formwould relay the database error message to the user. More sophisticatedforms may have built-in error handling functionality to programmaticallyhandle data related errors.

FIG. 4B illustrates an exemplary GUI screen that contains a form usedfor generating an insert statement. This screen is provided when theuser clicks on the insert plug-in component 431 from the FIG. 4A.Suppose the user selects a field from the base query for which theywould like to add a new value. The field chosen from the base querycorresponds to a column in a relational table. In order to add a newvalue for the column, a new record, including values for all requiredcolumns, needs to be added to the table. The fields 402 shown representall the columns of the table in which the new record will be inserted.Further, these fields 402 are presented as text boxes that allow usersto input desired values. Some of the fields may be pre-populated with adefault value as shown in field 402 _(B). Yet other fields may permitnull values and may have a check box 401 placed adjacent the field toallow users to set the field's value to “NULL”.

If at any time the user decides to cancel the transaction, the cancelbutton 411 can be clicked to clear the text boxes, terminate thetransaction and exit the screen without submitting any transactions.However, if the user wants to proceed with the transaction, the insertbutton 410B can be pushed; an insert statement incorporating the valuesentered in the fields 402 is assembled and submitted to the database.The transaction is not committed to the database, however. The user willget an opportunity to confirm their decision to perform this transactionin the next screen illustrated in FIG. 4C.

FIG. 4C illustrates a confirmation screen for the insert statementdescribed above. This is a standard confirmation screen that may appearafter a transaction has been submitted, but not yet committed to thedatabase. While not illustrated in the figures, similar typeconfirmation screens may appear after each of the exemplary databasedata manipulation transactions (e.g., insert, update, and delete)discussed in this application.

In most relational database systems, data related changes made by one ormore SQL statements constitute a transaction, and can be eithercommitted or rolled back. After a transaction is committed or rolledback, the next transaction in line begins with its SQL statements.Committing a transaction makes permanent the changes resulting from allSQL statements in the transaction. The changes made by the SQLstatements of a transaction become visible to other user's sessions onlyafter the transaction is committed. Rolling back a transaction retractsany of the changes resulting from the SQL statements in the transaction.After a transaction is rolled back, the affected data is left unchanged,as if the SQL statements in the transaction were never run.

If the user wishes to undo the current transaction, they would push thecancel button 421 causing the transaction to be rolled back—meaning thatthe insert is not written permanently to the database. However, if theuser decides to keep the transaction and write it permanently to thedatabase, the ok button 420 is pushed. Pushing this button will performa commit and permanently change data in the database.

Update Statements

Creation of an update statement proceeds by providing a form at step 355(from FIG. 3B) that is used to generate the update statement. As withother types of transactions, user information and field attributes fromthe base query are gathered and analyzed. While the base query maycontain only a subset of all the fields that comprise a record for aparticular table, the update form displays all the table's fields. Notall the fields may be editable, however, because restrictions related tosecurity and data integrity may not allow for the changing of values forcertain fields.

FIG. 4D illustrates an exemplary GUI screen containing a form designedto generate an update statement. Fields and their corresponding valuesfrom base queries are utilized to create this form. In this example, thebase query contained the KEY field 403 with the value of “2”; the keyfield corresponds to a column in a table called “stages”. If the userchooses to update a record based on its key value, a record in thestages table will need to be updated via the form shown in FIG. 4D.

All the columns in the stages table are represented by fields on theform. The key field 403 is a list box that allows users to scrollthrough key values. All other fields on this form are text boxes withvalues that correspond to the value stored in the column for aparticular record. The user is able to see an entire record in thestages table. As different key values are selected, users are able toview the related records. Whether a field is updatable is determined bythe field's attributes and related metadata. Once the user has selectedthe desired record and completed making changes to field values, theupdate button 410 _(C) can be clicked to submit the transaction.

Some base queries may require parameters to be entered. For example, abase query might have the following structure:

-   -   SELECT col1, col2, col3 FROM table x, table y WHERE col1=‘Blue’        and col2=?

In this case, the user may be prompted for a col2 value in order tocomplete the where clause portion of the query to identify a row to beupdated. A separate form (GUI screen) may be provided to allow users toenter the desired value for the parameter. Once an acceptable value isentered (e.g., a valid col2 value), the base query is executed and therecord(s) constituting the result set are retrieved and utilized.Similar forms may be utilized to specify a rows to be used in deletestatements (described below).

If the base query relates to multiple fields or values that areassociated with multiple records, a form with multiple records, such asthe one illustrated in FIG. 4E, may be generated. In some cases, thefields and values displayed in FIG. 4E may be obtained as results of aquery using user-input parameters, as described above. Check boxes 401_(F) are provided so that users can indicate which records are to beupdated. Even though multiple records are being modified, all of thesechanges are part of one transaction. In this case, 3 records are beingupdated. If, for example, data integrity related issues prevents one ofthe records from being updated, the entire transaction will be rolledback. In other words, none of the changes, even for records where therewasn't a problem, will go into effect.

Delete Statements

Delete statements are generated via a form at step 365 on FIG. 3B. Aswith the update process described above, all fields of a selected recordare displayed. This allows users to review values for all the fields andconfirm that the record selected is the correct record to be deleted.

FIGS. 4F and 4G illustrate forms that create statements for deletingsingle and multiple records, respectively. As with the update formsdescribed above, all the columns of the corresponding table arerepresented by fields. The fields are not editable, however, they aresimply provided to allow users to review the values and confirm that thecorrect record is selected for deletion.

As those skilled in the art will recognize, all three types oftransactions described above are subject to restrictions related todatabase security and data integrity. For example, if a user does nothave privileges to perform updates and deletes on certain tables, allattempts to submit update and delete transactions will result in adatabase management system error. Errors will also occur if atransaction violates existing constraints such as primary keys, foreignkeys and check constraints.

The example forms described above present fields and records from asingle table. However, those skilled in the art will recognize themethods described herein may be used with transactions affecting recordsfrom multiple tables which may be located in various databases in adistributed database environment.

While the foregoing is directed to embodiments of the present invention,other and further embodiments of the invention may be devised withoutdeparting from the basic scope thereof, and the scope thereof isdetermined by the claims that follow.

1. A method for generating a transactional database statement based onan existing database statement, comprising: parsing the existingdatabase statement to identify fields and corresponding fieldattributes; utilizing one or more interfaces for receiving inputregarding the transactional statement; and generating the transactionaldatabase statement based on the identified fields and user input.
 2. Themethod of claim 1, wherein the transactional database statement is oneof: an insert statement, an update statement, and a delete statement. 3.The method of claim 2, wherein generating the transactional databasestatement based on the identified fields and user input comprises:generating a transactional statement with one or more of the identifiedfields; and populating one or more of the fields with input received viathe one or more interfaces.
 4. The method of claim 1, further comprisingattaining metadata regarding one or more of the identified fields. 5.The method of claim 1, wherein utilizing the one or more interfacescomprises: generating at least one graphical user interface forreceiving input from a user, wherein the graphical user interface allowsthe user to specify one or more records to affect with the transactionalstatement.
 6. The method of claim 5, wherein utilizing the one or moreinterfaces further comprises: issuing a query to retrieve data relatedto at least one record specified by the user via the graphical userinterface; and displaying the data retrieved in the graphical userinterface.
 7. The method of claim 6, wherein: the transactionalstatement is an update statement; the graphical user interface allowsthe user to make changes to at least a portion of the retrieved data;and submit the changes to the database via the transactional databasestatement.
 8. The method of claim 7, wherein: the graphical userinterface allows the make changes data related to more than one record;and submit the changes to the database via the transactional databasestatement.
 9. The method of claim 5, further comprising providing theuser access to the graphical user interface screen via a plug-incomponent to an application.
 10. A computer-readable medium containing aprogram for generating a transactional database statement based on anexisting database statement which, when executed by a processor,performs operations comprising: parsing the existing database statementto identify fields and corresponding field attributes; generating one ormore interfaces for receiving input regarding the transactionalstatement; and generating the transactional database statement based onthe identified fields and user input.
 11. The computer-readable mediumof claim 10, wherein the transactional database statement is one of: aninsert statement, an update statement, and a delete statement.
 12. Thecomputer-readable medium of claim 10, wherein generating thetransactional database statement based on the identified fields and userinput comprises: generating a transactional statement with one or moreof the identified fields; and populating one or more of the fields withinput received via the one or more interfaces.
 13. The computer-readablemedium of claim 10, wherein generating the one or more interfacescomprises: generating at least one graphical user interface forreceiving input from a user, wherein the graphical user interface allowsthe user to specify one or more records to affect with the transactionalstatement.
 14. The computer-readable medium of claim 13, whereingenerating the one or more interfaces further comprises: issuing a queryto retrieve data related to at least one record specified by the uservia the graphical user interface; and displaying the data retrieved inthe graphical user interface.
 15. A data processing system comprising: adatabase; an existing query statement; a query interface allowing usersto issue query statements against the database; and a transactionmanager configured to generate a transactional statement against thedatabase based on fields and corresponding field attributes of theexisting query statement.
 16. The data processing system of claim 15,wherein the transaction manager is configured to generate thetransactional statement against the database by: parsing the existingdatabase statement to identify the fields and corresponding fieldattributes; generating one or more interfaces for receiving inputregarding the transactional statement; and generating the transactionaldatabase statement based on the identified fields and user input. 17.The data processing system of claim 15, wherein the transaction manageris configured to attain, from the database, metadata for use ingenerating the transactional statement.
 18. The data processing systemof claim 15, further comprising one or more plug-in components allowingaccess to the one or more interfaces from the query interface.
 19. Thedata processing system of claim 18, wherein the one or more plug-incomponents provide access to an interface for entering input to be usedin an insert transactional statement.
 20. The data processing system ofclaim 18, wherein the one or more plug-in components provide access toan interface for updating a record.
 21. The data processing system ofclaim 20, wherein the one or more plug-in components provide access toan interface for updating multiple records.
 21. The data processingsystem of claim 20, wherein the database is one of: a relationaldatabase, an object-relational database, an XML database, and arelational database.